-- 1.Create table DB_SCHEDULE_SERVER
CREATE TABLE DB_SCHEDULE_SERVER(
  ip  VARCHAR2(20) NOT NULL ,
  port  VARCHAR2(10) NOT NULL ,
  server_id VARCHAR2(300) NOT NULL ,
  schedule_switch VARCHAR2(1) NOT NULL ,
  core_pool_size  INTEGER NOT NULL ,
  max_pool_size INTEGER NOT NULL ,
  keep_alive_time VARCHAR2(30) NOT NULL ,
  start_delay_time VARCHAR2(30) NOT NULL ,
  heartbeat_time TIMESTAMP(3) DEFAULT systimestamp NOT NULL ,
  alive VARCHAR2(1) DEFAULT 'Y' NOT NULL ,
  wakeup VARCHAR2(1) DEFAULT 'N' NOT NULL ,
  version INTEGER DEFAULT 1 NOT NULL ,
  constraint PK_DB_SCHEDULE_SERVER primary key(ip, port)
);
comment on table DB_SCHEDULE_SERVER is '分布式调度服务器信息表';
comment on column DB_SCHEDULE_SERVER.ip is '服务器IP地址';
comment on column DB_SCHEDULE_SERVER.port is '服务器端口号';
comment on column DB_SCHEDULE_SERVER.server_id is '服务器标识，默认：服务器IP地址:服务器端口号';
comment on column DB_SCHEDULE_SERVER.schedule_switch is '调度器开关（关闭:0，打开：1）';
comment on column DB_SCHEDULE_SERVER.core_pool_size is '核心线程池大小';
comment on column DB_SCHEDULE_SERVER.max_pool_size is '线程池最大容量';
comment on column DB_SCHEDULE_SERVER.keep_alive_time is '当线程数大于核心线程数时,空闲线程存活时间,支持单位(天:d/D,时:h/H,分:m/M,秒:s/S,毫秒:ms/MS),不写单位默认秒';
comment on column DB_SCHEDULE_SERVER.start_delay_time is '第一次启动时延迟启动时间,支持单位(天:d/D,时:h/H,分:m/M,秒:s/S,毫秒:ms/MS),不写单位默认秒';
comment on column DB_SCHEDULE_SERVER.heartbeat_time is '最近一次心跳时间';
comment on column DB_SCHEDULE_SERVER.alive is '服务器是否存活(是：Y，否：N),若停止心跳30秒以上认为该服务器死亡';
comment on column DB_SCHEDULE_SERVER.wakeup is '是否立即唤醒调度器(是：Y，否：N)，当手动修改了任务信息时需要设置为Y，否则手动修改任务信息不会立即生效';
comment on column DB_SCHEDULE_SERVER.version is '乐观锁';

-- 2.Create table DB_SCHEDULE_TASK
CREATE TABLE DB_SCHEDULE_TASK(
  id  INTEGER NOT NULL ,
  task_id VARCHAR2(300) NOT NULL ,
  task_group VARCHAR2(300) ,
  task_step INTEGER ,
  task_name VARCHAR2(300) ,
  task_switch VARCHAR2(1) DEFAULT 'O' NOT NULL ,
  batch_no VARCHAR2(50) ,
  partition_count INTEGER DEFAULT 10 NOT NULL ,
  partition_mode VARCHAR2(1) DEFAULT 'S' NOT NULL,
  task_type VARCHAR2(2) DEFAULT 'N' NOT NULL ,
  bean_id VARCHAR2(50),
  bean_class VARCHAR2(300),
  remote_url VARCHAR2(300),
  mq_config_id VARCHAR2(30),
  mq_destination VARCHAR2(300),
  mq_callback VARCHAR2(300),
  parameters CLOB ,
  priority INTEGER NOT NULL ,
  status  VARCHAR2(2) NOT NULL ,
  cron_expression VARCHAR2(300) ,
  current_fire_time TIMESTAMP(3) ,
  current_finish_time TIMESTAMP(3) ,
  next_fire_time TIMESTAMP(3) ,
  error_message VARCHAR2(1000) ,
  dependent_task_id VARCHAR2(500) ,
  dependence_policy VARCHAR2(1) DEFAULT 'S' NOT NULL ,
  block_policy VARCHAR2(1) DEFAULT 'C' NOT NULL ,
  error_policy VARCHAR2(1) DEFAULT 'R' NOT NULL ,
  retry_policy VARCHAR2(1),
  retry_count INTEGER,
  retry_index INTEGER,
  retry_interval VARCHAR2(30),
  use_white_black_list VARCHAR2(1) DEFAULT 'N' NOT NULL ,
  alarm VARCHAR2(1) DEFAULT 'Y' NOT NULL ,
  version INTEGER DEFAULT 1 NOT NULL ,
  constraint PK_DB_SD_TASK primary key(id)
);
comment on table DB_SCHEDULE_TASK is '分布式调度任务信息表';
comment on column DB_SCHEDULE_TASK.id is '物理主键';
comment on column DB_SCHEDULE_TASK.task_id is '任务ID';
comment on column DB_SCHEDULE_TASK.task_group is '任务组,若为空则表示该任务为单体任务,分组后一个任务组为一个整体按照task_step顺序执行';
comment on column DB_SCHEDULE_TASK.task_step is '任务组步骤,任务组不为空时有效,同一个任务组可有相同的step表示可以同时执行';
comment on column DB_SCHEDULE_TASK.task_name is '任务名称';
comment on column DB_SCHEDULE_TASK.task_switch is '任务开关(打开:O,关闭:C)';
comment on column DB_SCHEDULE_TASK.batch_no is '批次号(一个主任务从开始到结束为一个批次,用于记录日志信息,N开头表示一般批次,R开头表示失败重试批次)';
comment on column DB_SCHEDULE_TASK.partition_count is '分区总数量(最大1000)';
comment on column DB_SCHEDULE_TASK.partition_mode is '分区模式(静态:S,动态智能:D)';
comment on column DB_SCHEDULE_TASK.task_type is '任务类型(本地:N,远程:R,MQ:M)';
comment on column DB_SCHEDULE_TASK.bean_id is '任务类型为本地时有效,spring已注册的beanId,与bean_class设置其一即可,若都设置优先级高于bean_class';
comment on column DB_SCHEDULE_TASK.bean_class is '任务类型为本地时有效,任务执行类的完整类名,优先级低于bean_id';
comment on column DB_SCHEDULE_TASK.remote_url is '任务类型为远程时有效,远程调用的url地址';
comment on column DB_SCHEDULE_TASK.mq_config_id is '任务类型为MQ时有效,与表db_schedule_mq_config关联';
comment on column DB_SCHEDULE_TASK.mq_destination is '任务类型为MQ时有效,MQ的发送目标地址';
comment on column DB_SCHEDULE_TASK.mq_callback is '任务类型为MQ时有效,MQ的回调通知地址';
comment on column DB_SCHEDULE_TASK.parameters is '任务执行参数,使用(key1=value1,key2=value2...)的格式';
comment on column DB_SCHEDULE_TASK.priority is '优先级(1-7),数值越大优先级越高';
comment on column DB_SCHEDULE_TASK.status is '状态(未初始化:N,正在初始化:I,初始化失败:IF,待命:S,阻塞待处理:B,异常重试-阻塞待处理:RB,处理中:R,
异常重试-处理中:RR,暂停:P,异常重试-暂停:RP,处理异常:E,致命错误:F,处理完成:C)';
comment on column DB_SCHEDULE_TASK.cron_expression is '周期性任务的cron表达式';
comment on column DB_SCHEDULE_TASK.current_fire_time is '本次任务执行时间';
comment on column DB_SCHEDULE_TASK.current_finish_time is '本次任务完成时间';
comment on column DB_SCHEDULE_TASK.next_fire_time is '下次任务执行时间';
comment on column DB_SCHEDULE_TASK.error_message is '错误信息';
comment on column DB_SCHEDULE_TASK.dependent_task_id is '依赖的任务ID';
comment on column DB_SCHEDULE_TASK.dependence_policy is '依赖策略(强依赖:S,弱依赖:W)';
comment on column DB_SCHEDULE_TASK.block_policy is '任务阻塞策略(常规模式:C、幂等模式:I)';
comment on column DB_SCHEDULE_TASK.error_policy is '异常策略(重试:R、人工:M、忽略:I)';
comment on column DB_SCHEDULE_TASK.retry_policy is '重试策略(无:N,递进:G,指数:E)';
comment on column DB_SCHEDULE_TASK.retry_count is '重试次数';
comment on column DB_SCHEDULE_TASK.retry_index is '当前已重试次数';
comment on column DB_SCHEDULE_TASK.retry_interval is '重试间隔,支持单位(天:d/D,时:h/H,分:m/M,秒:s/S,毫秒:ms/MS),不写单位默认秒';
comment on column DB_SCHEDULE_TASK.use_white_black_list is '是否使用白、黑名单(是:Y，否:N)';
comment on column DB_SCHEDULE_TASK.alarm is '是否错误报警(是:Y，否:N)';
comment on column DB_SCHEDULE_TASK.version is '乐观锁';
create unique index UIX_DB_SE_TK_TK_ID on DB_SCHEDULE_TASK(task_id);
create index IDX_DB_SE_TASK_GROUP on DB_SCHEDULE_TASK(task_group);
create index IDX_DB_SE_TASK_PRIORITY on DB_SCHEDULE_TASK(priority);
create index IDX_DB_SE_TASK_MQ_CONFIG on DB_SCHEDULE_TASK(mq_config_id);

-- 3.Create table DB_SCHEDULE_SUB_TASK
CREATE TABLE DB_SCHEDULE_SUB_TASK(
  id  INTEGER NOT NULL ,
  parent_id  INTEGER NOT NULL ,
  partition_count INTEGER NOT NULL ,
  partition_index INTEGER NOT NULL ,
  status  VARCHAR2(2) NOT NULL ,
  error_message VARCHAR2(1000) ,
  execute_server_ip VARCHAR2(20) ,
  execute_server_port VARCHAR2(10) ,
  version INTEGER DEFAULT 1 NOT NULL ,
  constraint PK_DB_SD_SUB_TASK primary key (id)
);
comment on table DB_SCHEDULE_SUB_TASK is '分布式调度子任务信息表';
comment on column DB_SCHEDULE_SUB_TASK.id is '物理主键';
comment on column DB_SCHEDULE_SUB_TASK.parent_id is '主任务ID';
comment on column DB_SCHEDULE_SUB_TASK.partition_count is '分区总数量';
comment on column DB_SCHEDULE_SUB_TASK.partition_index is '当前分区索引';
comment on column DB_SCHEDULE_SUB_TASK.status is '状态';
comment on column DB_SCHEDULE_SUB_TASK.error_message is '错误信息';
comment on column DB_SCHEDULE_SUB_TASK.execute_server_ip is '执行该子任务的服务器IP';
comment on column DB_SCHEDULE_SUB_TASK.execute_server_port is '执行该子任务的服务器端口';
comment on column DB_SCHEDULE_SUB_TASK.version is '乐观锁';
create index IDX_DB_SE_SUB_PARENT_ID on DB_SCHEDULE_SUB_TASK(parent_id);

-- 4.Create table DB_SCHEDULE_WHITE_BLACK_LIST
CREATE TABLE DB_SCHEDULE_WHITE_BLACK_LIST(
  task_id  INTEGER NOT NULL ,
  server_ip VARCHAR2(20) NOT NULL ,
  server_port VARCHAR2(10) NOT NULL ,
  direction  VARCHAR2(1) NOT NULL ,
  constraint PK_DB_SD_W_B_LIST primary key (task_id, server_ip, server_port)
);
comment on table DB_SCHEDULE_WHITE_BLACK_LIST is '分布式调度任务白、黑名单';
comment on column DB_SCHEDULE_WHITE_BLACK_LIST.task_id is '主任务主键';
comment on column DB_SCHEDULE_WHITE_BLACK_LIST.server_ip is '服务器IP';
comment on column DB_SCHEDULE_WHITE_BLACK_LIST.server_port is '服务器端口号';
comment on column DB_SCHEDULE_WHITE_BLACK_LIST.direction is '所属方向(白名单:W,黑名单:B)';

-- 5.Create table DB_SCHEDULE_TASK_RECORD
CREATE TABLE DB_SCHEDULE_TASK_RECORD(
  task_id INTEGER NOT NULL ,
  batch_no VARCHAR2(50) NOT NULL ,
  prev_batch_no VARCHAR2(50) ,
  fire_time TIMESTAMP(3) ,
  finish_time TIMESTAMP(3) ,
  used_time INTEGER ,
  status VARCHAR2(2) ,
  constraint PK_DB_SD_TK_RD primary key (task_id, batch_no)
);
comment on table DB_SCHEDULE_TASK_RECORD is '分布式调度主任务执行记录表';
comment on column DB_SCHEDULE_TASK_RECORD.task_id is '主任务主键';
comment on column DB_SCHEDULE_TASK_RECORD.batch_no is '主任务执行批次号';
comment on column DB_SCHEDULE_TASK_RECORD.prev_batch_no is '主任务执行前一批次号';
comment on column DB_SCHEDULE_TASK_RECORD.fire_time is '主任务执行开始时间';
comment on column DB_SCHEDULE_TASK_RECORD.finish_time is '主任务执行结束时间';
comment on column DB_SCHEDULE_TASK_RECORD.used_time is '主任务执行用时(毫秒)';
comment on column DB_SCHEDULE_TASK_RECORD.status is '主任务执行状态(处理异常:E,致命错误:F,处理完成:C)';

-- 6.Create table DB_SCHEDULE_SUB_TASK_RECORD
CREATE TABLE DB_SCHEDULE_SUB_TASK_RECORD(
  sub_task_id INTEGER NOT NULL ,
  batch_no VARCHAR2(50) NOT NULL ,
  parent_id INTEGER NOT NULL ,
  partition_count INTEGER NOT NULL ,
  partition_index INTEGER NOT NULL ,
  fire_time TIMESTAMP(3) ,
  finish_time TIMESTAMP(3) ,
  used_time INTEGER ,
  status VARCHAR2(2) ,
  error_message CLOB ,
  execute_server_ip VARCHAR2(20) ,
  execute_server_port VARCHAR2(10) ,
  execute_thread_id INTEGER ,
  constraint PK_DB_SD_SUB_TK_RD primary key (sub_task_id, batch_no)
);
comment on table DB_SCHEDULE_SUB_TASK_RECORD is '分布式调度子任务执行记录表';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.sub_task_id is '子任务主键';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.batch_no is '主任务执行批次号';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.parent_id is '主任务主键';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.partition_count is '分区总数量';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.partition_index is '分区索引';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.fire_time is '子任务执行开始时间';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.finish_time is '子任务执行结束时间';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.used_time is '子任务执行用时(毫秒)';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.status is '子任务执行状态(处理异常:E,致命错误:F,处理完成:C)';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.error_message is '子任务执行错误信息';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.execute_server_ip is '子任务执行服务器IP';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.execute_server_port is '子任务执行服务器端口';
comment on column DB_SCHEDULE_SUB_TASK_RECORD.execute_thread_id is '子任务执行服务器线程编号';
create index IDX_DB_SD_SUB_TK_RD_TK_ID on DB_SCHEDULE_SUB_TASK_RECORD(parent_id);
create index IDX_DB_SD_SUB_TK_RD_SERVER on DB_SCHEDULE_SUB_TASK_RECORD(execute_server_ip, execute_server_port, execute_thread_id);

-- 7.Create table DB_SCHEDULE_MQ_CONFIG
CREATE TABLE DB_SCHEDULE_MQ_CONFIG(
  mq_config_id VARCHAR2(30) NOT NULL ,
  mq_type VARCHAR2(2) NOT NULL ,
  mq_connection VARCHAR2(1000) NOT NULL ,
  mq_user VARCHAR2(300) ,
  mq_password VARCHAR2(300) ,
  mq_producer_group_name VARCHAR2(300) ,
  mq_consumer_group_name VARCHAR2(300) ,
  constraint PK_DB_SD_MQ_CONFIG primary key (mq_config_id)
);
comment on table DB_SCHEDULE_MQ_CONFIG is '分布式调度MQ配置表';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_config_id is '物理主键';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_type is 'mq类型(ActiveMQ:A,RocketMQ:R)';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_connection is '如果是rocketmq则代表nameServer，如果是activemq则代表brokerUrl';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_user is '连接mq的用户名';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_password is '连接mq的密码';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_producer_group_name is 'rocketmq的生产者groupName,若为空默认"DISTRIBUTED_PRODUCER"';
comment on column DB_SCHEDULE_MQ_CONFIG.mq_consumer_group_name is 'rocketmq的消费者groupName,若为空默认"DISTRIBUTED_CONSUMER"';

-- 8.Create table DB_SCHEDULE_USER_INFO
CREATE TABLE DB_SCHEDULE_USER_INFO(
  user_name VARCHAR2(100) NOT NULL ,
  user_password VARCHAR2(300) ,
  login_time TIMESTAMP(3) ,
  login_token VARCHAR2(100) ,
  page_layout VARCHAR2(2) NOT NULL ,
  page_language VARCHAR2(2) NOT NULL ,
  page_lightup VARCHAR2(1) NOT NULL ,
  page_theme VARCHAR2(10) NOT NULL ,
  dashboard_content VARCHAR2(1000) ,
  version INTEGER DEFAULT 1 NOT NULL ,
  constraint PK_DB_SD_USER_CONFIG primary key (user_name)
);
comment on table DB_SCHEDULE_USER_INFO is '分布式调度用户信息表';
comment on column DB_SCHEDULE_USER_INFO.user_name is '用户名';
comment on column DB_SCHEDULE_USER_INFO.user_password is '用户密码';
comment on column DB_SCHEDULE_USER_INFO.login_time is '登录时间';
comment on column DB_SCHEDULE_USER_INFO.login_token is '登录标识';
comment on column DB_SCHEDULE_USER_INFO.page_layout is '页面布局(左:L,上:T)';
comment on column DB_SCHEDULE_USER_INFO.page_language is '页面语言(简体中文:Z,英文:E)';
comment on column DB_SCHEDULE_USER_INFO.page_lightup is '是否启动开灯模式(是:Y,否:N)';
comment on column DB_SCHEDULE_USER_INFO.page_theme is '页面主题颜色';
comment on column DB_SCHEDULE_USER_INFO.dashboard_content is 'dashboard页面的内容ID及顺序，模块之间用","分割';
comment on column DB_SCHEDULE_USER_INFO.version is '乐观锁';

-- 9.Create table DB_SCHEDULE_CONFIG
CREATE TABLE DB_SCHEDULE_CONFIG(
  version VARCHAR2(50) NOT NULL ,
  version_iteration INTEGER NOT NULL ,
  open_login VARCHAR2(1) DEFAULT 'Y' NOT NULL ,
  native_login VARCHAR2(1) NOT NULL ,
  remote_login_url VARCHAR2(300) ,
  use_sso VARCHAR2(1) DEFAULT 'Y' NOT NULL ,
  session_timeout VARCHAR2(30) DEFAULT '30m' NOT NULL ,
  route_policy VARCHAR2(2) DEFAULT 'S' NOT NULL
);
comment on table DB_SCHEDULE_CONFIG is '分布式调度总配置表';
comment on column DB_SCHEDULE_CONFIG.version is '版本号';
comment on column DB_SCHEDULE_CONFIG.version_iteration is '版本编号';
comment on column DB_SCHEDULE_CONFIG.open_login is '是否开启登录模式(是:Y,否:N)';
comment on column DB_SCHEDULE_CONFIG.native_login is '是否使用本地登录(是:Y,否:N),如果是则使用本表中密码登录,如果否则使用配置中的远程登录地址';
comment on column DB_SCHEDULE_CONFIG.remote_login_url is '远程登录地址';
comment on column DB_SCHEDULE_CONFIG.use_sso is '若开启登录模式，是否使用单点登录模式(是:Y,否:N)';
comment on column DB_SCHEDULE_CONFIG.session_timeout is '若开启登录模式，登录的会话超时时间，支持单位(天:d/D,时:h/H,分:m/M,秒:s/S,毫秒:ms/MS),不写单位默认秒';
comment on column DB_SCHEDULE_CONFIG.route_policy is '路由策略(争抢:S,轮询:P,权重:W)';